CREATE OR REPLACE EDITIONABLE PACKAGE BODY "SCOTT"."PKG_COMMODITY_FUTURE_DATE_DATA" is

  /*-- 计算日下级别全部last_close_price、rising_and_falling_amount和price_change字段 --*/
  procedure WRITE_BASIC_DATA is
    -- 期货代码
    v_code varchar2(100);
    -- 代表是否是某个期货的第一条记录
    v_first_row boolean := true;
    -- commodity_future_info表的游标
    cursor cur_commodity_future_info is
      select * from commodity_future_info;
    -- commodity_future_date_data表的游标
    cursor cur_commodity_future_date_data is
      select *
        from commodity_future_date_data cfdd
       where cfdd.code = v_code
       order by cfdd.transaction_date asc;
    -- commodity_future_info类型的记录
    row_commodity_future_info commodity_future_info%rowtype;
    -- commodity_future_date_data类型的记录
    row_commodity_future_date_data commodity_future_date_data%rowtype;
    -- commodity_future_date_data类型的记录（上一条）
    row_last_c_f_date_data commodity_future_date_data%rowtype;
  begin
    open cur_commodity_future_info;
    loop
      -- 获取每个期货信息
      fetch cur_commodity_future_info
        into row_commodity_future_info;
      exit when cur_commodity_future_info%notfound;
    
      -- 重置
      v_code      := row_commodity_future_info.code;
      v_first_row := true;
    
      open cur_commodity_future_date_data;
      loop
        -- 获取每个期货的交易记录
        fetch cur_commodity_future_date_data
          into row_commodity_future_date_data;
        exit when cur_commodity_future_date_data%notfound;
      
        if v_first_row = true then
          -- 更新rising_and_falling_amount和price_change字段。第一天使用当天的开盘价
          update commodity_future_date_data t
             set t.rising_and_falling_amount = row_commodity_future_date_data.close_price -
                                               row_commodity_future_date_data.open_price,
                 t.price_change             =
                 (row_commodity_future_date_data.close_price -
                 row_commodity_future_date_data.open_price) /
                 row_commodity_future_date_data.open_price * 100,
                 t.last_close_price          = row_commodity_future_date_data.open_price
           where t.code = v_code
             and t.transaction_date =
                 row_commodity_future_date_data.transaction_date;
        
          v_first_row            := false;
          row_last_c_f_date_data := row_commodity_future_date_data;
          continue;
        else
          -- 更新last_close_price字段
          update commodity_future_date_data t
             set t.last_close_price = row_last_c_f_date_data.close_price
           where t.code = v_code
             and t.transaction_date =
                 row_commodity_future_date_data.transaction_date;
        
          -- 更新rising_and_falling_amount和price_change字段
          update commodity_future_date_data t
             set t.rising_and_falling_amount = row_commodity_future_date_data.close_price -
                                               row_last_c_f_date_data.close_price,
                 t.price_change             =
                 (row_commodity_future_date_data.close_price -
                 row_last_c_f_date_data.close_price) /
                 row_last_c_f_date_data.close_price * 100
           where t.code = v_code
             and t.transaction_date =
                 row_commodity_future_date_data.transaction_date;
        
          row_last_c_f_date_data := row_commodity_future_date_data;
        
        end if;
      
      end loop;
      close cur_commodity_future_date_data;
    
    end loop;
    close cur_commodity_future_info;
  
    commit;
  end WRITE_BASIC_DATA;
  -- end procedure

  /*-- 计算某一天所有期货的last_close_price、rising_and_falling_amount和price_change字段 --*/
  procedure WRITE_BASIC_DATA_BY_DATE(p_date in varchar2) as
    -- 期货代码
    v_code varchar2(100);
    -- 代表是否是某个期货的第一条记录
    v_first_row boolean := true;
    -- commodity_future_info表的游标
    cursor cur_commodity_future_info is
      select * from commodity_future_info;
    -- commodity_future_date_data表的游标，最近的两条记录
    cursor cur_commodity_future_date_data is
      select *
        from (select *
                from (select *
                        from commodity_future_date_data cfdd
                       where cfdd.code = v_code
                         and cfdd.transaction_date <=
                             to_date(p_date, 'yyyy-mm-dd')
                       order by cfdd.transaction_date desc)
               where rownum <= 2) t1
       order by t1.transaction_date asc;
    -- commodity_future_info类型的记录
    row_commodity_future_info commodity_future_info%rowtype;
    -- commodity_future_date_data类型的记录
    row_commodity_future_date_data commodity_future_date_data%rowtype;
    -- commodity_future_date_data类型的记录（上一条）
    row_last_c_f_date_data commodity_future_date_data%rowtype;
  begin
    open cur_commodity_future_info;
    loop
      -- 获取每个期货信息
      fetch cur_commodity_future_info
        into row_commodity_future_info;
      exit when cur_commodity_future_info%notfound;
    
      -- 重置
      v_code      := row_commodity_future_info.code;
      v_first_row := true;
    
      open cur_commodity_future_date_data;
      loop
        -- 获取每个期货的交易记录
        fetch cur_commodity_future_date_data
          into row_commodity_future_date_data;
        exit when cur_commodity_future_date_data%notfound;
      
        if v_first_row = true then
          v_first_row            := false;
          row_last_c_f_date_data := row_commodity_future_date_data;
          continue;
        else
          -- 更新last_close_price字段
          update commodity_future_date_data t
             set t.last_close_price = row_last_c_f_date_data.close_price
           where t.code = v_code
             and t.transaction_date = to_date(p_date, 'yyyy-mm-dd');
        
          -- 更新rising_and_falling_amount和price_change字段
          update commodity_future_date_data t
             set t.rising_and_falling_amount = row_commodity_future_date_data.close_price -
                                               row_last_c_f_date_data.close_price,
                 t.price_change             =
                 (row_commodity_future_date_data.close_price -
                 row_last_c_f_date_data.close_price) /
                 row_last_c_f_date_data.close_price * 100
           where t.code = v_code
             and t.transaction_date = to_date(p_date, 'yyyy-mm-dd');
        
          row_last_c_f_date_data := row_commodity_future_date_data;
        end if;
      
      end loop;
      close cur_commodity_future_date_data;
    
    end loop;
    close cur_commodity_future_info;
  
    commit;
  end WRITE_BASIC_DATA_BY_DATE;
  -- end procedure

  /*-------------------------- 计算统一相对价格指数 ----------------------------*/
  procedure CAL_UNITE_RELATIVE_PRICE_INDEX is
    -- 代码
    v_code varchar(255);
    -- 统一相对价格指数
    v_unite_relative_price_index number;
    -- 某个期货上一个交易日的unite_relative_price_index字段
    v_last_u_r_p_i number;
    -- 交易日期
    v_transaction_date varchar(255);
    -- 上一个交易日
    v_last_transaction_date date;
    -- 是否是第一个交易日
    v_first_transaction_date boolean := true;
    -- 是否是某个期货的第一个交易日
    v_c_f_first_transaction_date boolean := true;
    -- commodity_future_date_data类型变量
    row_c_f_date_data_by_date commodity_future_date_data%rowtype;
    -- 查询交易日期，按升序排列
    cursor cur_transaction_date is
      select distinct to_char(t.transaction_date, 'yyyy-mm-dd')
        from commodity_future_date_data t
       order by to_char(t.transaction_date, 'yyyy-mm-dd') asc;
    -- 某一交易日的期货记录
    cursor cur_c_f_date_data_by_date is
      select *
        from commodity_future_date_data t
       where t.transaction_date = to_date(v_transaction_date, 'yyyy-mm-dd');
  begin
    open cur_transaction_date;
    loop
      fetch cur_transaction_date
        into v_transaction_date;
      exit when cur_transaction_date%notfound;
    
      open cur_c_f_date_data_by_date;
      if v_first_transaction_date then
        -- 是第一个交易日
        loop
          fetch cur_c_f_date_data_by_date
            into row_c_f_date_data_by_date;
          exit when cur_c_f_date_data_by_date%notfound;
        
          if row_c_f_date_data_by_date.code = 'AP' then
            null;
          end if;
        
          update commodity_future_date_data t
             set t.unite_relative_price_index = 100 * (1 +
                                                row_c_f_date_data_by_date.price_change / 100)
           where t.transaction_date =
                 row_c_f_date_data_by_date.transaction_date
             and t.code = row_c_f_date_data_by_date.code;
        end loop;
      
        -- 以后的交易日使用
        v_first_transaction_date := false;
        v_last_transaction_date  := row_c_f_date_data_by_date.transaction_date;
      else
        -- 不是第一个交易日
        loop
          fetch cur_c_f_date_data_by_date
            into row_c_f_date_data_by_date;
          exit when cur_c_f_date_data_by_date%notfound;
        
          -- 如果当前日期是某个期货的第一个交易日期，则计算上一个交易日所有期货的平均unite_relative_price_index，作为这个期货的前一个交易日的unite_relative_price_index
          v_c_f_first_transaction_date := true;
          begin
            select *
              into v_code, v_transaction_date
              from (select t.code code,
                           min(t.transaction_date) transaction_date
                      from commodity_future_date_data t
                     group by t.code) t1
             where t1.code = row_c_f_date_data_by_date.code
               and t1.transaction_date =
                   row_c_f_date_data_by_date.transaction_date;
          exception
            when no_data_found then
              v_c_f_first_transaction_date := false;
          end;
        
          if v_c_f_first_transaction_date then
            -- 是某个期货的第一个交易日
            -- 计算当前交易日的上一个交易日的所有期货的平均unite_relative_price_index
            select avg(t.unite_relative_price_index)
              into v_unite_relative_price_index
              from commodity_future_date_data t
             where t.transaction_date = v_last_transaction_date;
          
            -- 更新
            update commodity_future_date_data t
               set t.unite_relative_price_index = v_unite_relative_price_index * (1 +
                                                  row_c_f_date_data_by_date.price_change / 100)
             where t.transaction_date =
                   row_c_f_date_data_by_date.transaction_date
               and t.code = row_c_f_date_data_by_date.code;
          else
            -- 不是某个期货的第一个交易日
          
            -- 查询某个期货上一个交易日的v_unite_relative_price_index字段
            select t1.unite_relative_price_index
              into v_last_u_r_p_i
              from (select *
                      from commodity_future_date_data t
                     where t.transaction_date <
                           row_c_f_date_data_by_date.transaction_date
                       and t.code = row_c_f_date_data_by_date.code
                     order by t.transaction_date desc) t1
             where rownum <= 1;
          
            -- 更新
            update commodity_future_date_data t
               set t.unite_relative_price_index = v_last_u_r_p_i * (1 +
                                                  row_c_f_date_data_by_date.price_change / 100)
             where t.transaction_date =
                   row_c_f_date_data_by_date.transaction_date
               and t.code = row_c_f_date_data_by_date.code;
          end if;
        end loop;
      end if;
      close cur_c_f_date_data_by_date;
    
      -- 以后的交易日使用
      v_last_transaction_date := row_c_f_date_data_by_date.transaction_date;
    end loop;
    close cur_transaction_date;
    commit;
  end CAL_UNITE_RELATIVE_PRICE_INDEX;

  /*-------------------------- 计算某一天的统一相对价格指数 ----------------------------*/
  procedure CAL_U_R_PRICE_INDEX_BY_DATE(p_date in varchar2) is
    -- 代码
    v_code varchar(255);
    -- 统一相对价格指数
    v_unite_relative_price_index number;
    -- 某个期货上一个交易日的unite_relative_price_index字段
    v_last_u_r_p_i number;
    -- 是否是某个期货的第一个交易日
    v_c_f_first_transaction_date boolean := true;
    -- commodity_future_date_data类型变量
    row_c_f_date_data_by_date commodity_future_date_data%rowtype;
    -- 某一交易日的期货记录
    cursor cur_c_f_date_data_by_date is
      select *
        from commodity_future_date_data t
       where t.transaction_date = to_date(p_date, 'yyyy-mm-dd');
  begin
    open cur_c_f_date_data_by_date;
    loop
      fetch cur_c_f_date_data_by_date
        into row_c_f_date_data_by_date;
      exit when cur_c_f_date_data_by_date%notfound;
    
      -- 如果当前日期是某个期货的第一个交易日期，则计算上一个交易日所有期货的平均unite_relative_price_index，作为这个期货的前一个交易日的unite_relative_price_index
      v_c_f_first_transaction_date := false;
      begin
        select t1.code
          into v_code
          from commodity_future_date_data t1
         where t1.transaction_date = to_date(p_date, 'yyyy-mm-dd')
           and t1.code = row_c_f_date_data_by_date.code
           and t1.code in
               (select distinct t.code code
                  from commodity_future_date_data t
                 where t.transaction_date < to_date(p_date, 'yyyy-mm-dd'));
      exception
        when no_data_found then
          v_c_f_first_transaction_date := true;
      end;
    
      if v_c_f_first_transaction_date then
        -- 是某个期货的第一个交易日
      
        -- 计算当前交易日的上一个交易日的所有期货的平均unite_relative_price_index
        select avg(t1.unite_relative_price_index)
          into v_unite_relative_price_index
          from commodity_future_date_data t1
         where t1.transaction_date =
               (select *
                  from (select *
                          from (select distinct t.transaction_date
                                  from commodity_future_date_data t
                                 where t.transaction_date <
                                       to_date(p_date, 'yyyy-mm-dd')
                                 order by t.transaction_date desc)) t2
                 where rownum <= 1);
      
        -- 更新
        update commodity_future_date_data t
           set t.unite_relative_price_index = v_unite_relative_price_index * (1 +
                                              row_c_f_date_data_by_date.price_change / 100)
         where t.transaction_date =
               row_c_f_date_data_by_date.transaction_date
           and t.code = row_c_f_date_data_by_date.code;
      else
        -- 不是某个期货的第一个交易日
      
        -- 查询某个期货上一个交易日的v_unite_relative_price_index字段
        select t1.unite_relative_price_index
          into v_last_u_r_p_i
          from (select *
                  from commodity_future_date_data t
                 where t.transaction_date <
                       row_c_f_date_data_by_date.transaction_date
                   and t.code = row_c_f_date_data_by_date.code
                 order by t.transaction_date desc) t1
         where rownum <= 1;
      
        -- 更新
        update commodity_future_date_data t
           set t.unite_relative_price_index = v_last_u_r_p_i * (1 +
                                              row_c_f_date_data_by_date.price_change / 100)
         where t.transaction_date =
               row_c_f_date_data_by_date.transaction_date
           and t.code = row_c_f_date_data_by_date.code;
      end if;
    end loop;
    close cur_c_f_date_data_by_date;
    commit;
  end CAL_U_R_PRICE_INDEX_BY_DATE;

  /*-------------------------------------------- calculate five moving average -----------------------------------------*/
  procedure CALCULATE_FIVE is
  begin
    ------------------------------------------ standard declare section ----------------------------------------------
    declare
      -- 表示code
      v_code varchar2(10);
      -- 表示5天收盘价的和
      v_five_sum number := 0;
      -- 表示5天收盘价的平均值
      v_five_average number := 0;
      -- 定义一个含有5个数值型数据的数组
      type type_array is varray(5) of number;
      array_five type_array := type_array();
      -- define cursor section.返回全部code
      cursor cur_all_code is
        select distinct cfdd.code from commodity_future_date_data cfdd;
      cursor cur_all_close_price is
        select cfdd.close_price, cfdd.transaction_date
          from commodity_future_date_data cfdd
         where cfdd.code = v_code
         order by cfdd.transaction_date asc;
      ------------------------------------------ standard declare section ---------------------------------------------------
    
    begin
      --------------------------------- standard for loop section --------------------------------------------------------
      for i in cur_all_code loop
        v_code := i.code;
        -- dbms_output.put_line('**************   v_code'||'  :  '||v_code);
        array_five := type_array();
        for j in cur_all_close_price loop
          array_five.extend; -- 扩展数组，扩展一个元素
          array_five(array_five.count) := j.close_price;
          if mod(array_five.count, 5) = 0 then
            -- dbms_output.put_line('j.date_'||'  :  '||j.date_);
            v_five_sum := 0;
            for x in 1 .. array_five.count loop
              -- 求5天收盘价的和
              v_five_sum := v_five_sum + array_five(x);
            end loop;
            -- dbms_output.put_line('v_five_sum'||'  :  '||v_five_sum);
            -- 删除数组中的第一个元素，将其与4个元素向前挪一位，并删除下标为5的元素
            for y in 1 .. array_five.count - 1 loop
              array_five(y) := array_five(y + 1);
            end loop;
            array_five.trim;
            -- 5天收盘价的平均值
            v_five_average := v_five_sum / 5;
            -- 向所有记录的FIVE列插入5天收盘价的平均值
            update commodity_future_date_data cfdd
               set cfdd.ma5 = round(v_five_average, 2)
             where cfdd.code = v_code
               and cfdd.transaction_date = j.transaction_date;
            -- dbms_output.put_line('v_five_average'||'  :  '||v_five_average);
          end if;
        end loop;
      end loop;
      commit;
    end;
  end CALCULATE_FIVE;
  -- end procedure

  /*---------------------------------------------------- calculate ten moving average -------------------------------------------------*/
  procedure CALCULATE_TEN is
  begin
    ------------------------------------------ standard declare section ---------------------------------------------------
    declare
      -- 表示code
      v_code varchar2(10);
      -- 表示10天收盘价的和
      v_ten_sum number := 0;
      -- 表示10天收盘价的平均值
      v_ten_average number := 0;
      -- 定义一个含有10个数值型数据的数组
      type type_array is varray(10) of number;
      array_ten type_array := type_array();
      -- define cursor section.返回全部code
      cursor cur_all_code is
        select distinct cfdd.code from commodity_future_date_data cfdd;
      cursor cur_all_close_price is
        select cfdd.close_price, cfdd.transaction_date
          from commodity_future_date_data cfdd
         where cfdd.code = v_code
         order by cfdd.transaction_date asc;
      ------------------------------------------ standard declare section ---------------------------------------------------
    
    begin
      --------------------------------- standard for loop section --------------------------------------------------------
      for i in cur_all_code loop
        v_code := i.code;
        -- dbms_output.put_line('**************   v_code'||'  :  '||v_code);
        array_ten := type_array();
        for j in cur_all_close_price loop
          array_ten.extend; -- 扩展数组，扩展一个元素
          array_ten(array_ten.count) := j.close_price;
          if mod(array_ten.count, 10) = 0 then
            -- dbms_output.put_line('j.date_'||'  :  '||j.date_);
            v_ten_sum := 0;
            for x in 1 .. array_ten.count loop
              -- 求10天收盘价的和
              v_ten_sum := v_ten_sum + array_ten(x);
            end loop;
            -- dbms_output.put_line('ten_sum'||'  :  '||v_ten_sum);
            -- 删除数组中的第一个元素，将其与9个元素向前挪一位，并删除下标为10的元素
            for y in 1 .. array_ten.count - 1 loop
              array_ten(y) := array_ten(y + 1);
            end loop;
            array_ten.trim;
            -- 10天收盘价的平均值
            v_ten_average := v_ten_sum / 10;
            -- 向所有记录的FIVE列插入10天收盘价的平均值
            update commodity_future_date_data cfdd
               set cfdd.ma10 = round(v_ten_average, 2)
             where cfdd.code = v_code
               and cfdd.transaction_date = j.transaction_date;
            -- dbms_output.put_line('v_ten_average'||'  :  '||v_ten_average);
          end if;
        end loop;
      end loop;
      commit;
    end;
  end CALCULATE_TEN;
  -- end procedure

  /*---------------------------------------------------- calculate twenty moving average -------------------------------------------------*/
  procedure CALCULATE_TWENTY is
  begin
    ------------------------------------------ standard declare section ---------------------------------------------------
    declare
      -- 表示code
      v_code varchar2(10);
      -- 表示20天收盘价的和
      v_twenty_sum number := 0;
      -- 表示20天收盘价的平均值
      v_twenty_average number := 0;
      -- 定义一个含有20个数值型数据的数组
      type type_array is varray(20) of number;
      array_twenty type_array := type_array();
      -- define cursor section.返回全部code
      cursor cur_all_code is
        select distinct cfdd.code from commodity_future_date_data cfdd;
      cursor cur_al_close_price is
        select cfdd.close_price, cfdd.transaction_date
          from commodity_future_date_data cfdd
         where cfdd.code = v_code
         order by cfdd.transaction_date asc;
      ------------------------------------------ standard declare section ---------------------------------------------------
    
    begin
      --------------------------------- standard for loop section --------------------------------------------------------
      for i in cur_all_code loop
        v_code := i.code;
        -- dbms_output.put_line('**************   v_code'||'  :  '||v_code);
        array_twenty := type_array();
        for j in cur_al_close_price loop
          array_twenty.extend; -- 扩展数组，扩展一个元素
          array_twenty(array_twenty.count) := j.close_price;
          if mod(array_twenty.count, 20) = 0 then
            -- dbms_output.put_line('j.date_'||'  :  '||j.date_);
            v_twenty_sum := 0;
            for x in 1 .. array_twenty.count loop
              -- 求20天收盘价的和
              v_twenty_sum := v_twenty_sum + array_twenty(x);
            end loop;
            -- dbms_output.put_line('v_twenty_sum'||'  :  '||v_twenty_sum);
            -- 删除数组中的第一个元素，将其与19个元素向前挪一位，并删除下标为20的元素
            for y in 1 .. array_twenty.count - 1 loop
              array_twenty(y) := array_twenty(y + 1);
            end loop;
            array_twenty.trim;
            -- 20天收盘价的平均值
            v_twenty_average := v_twenty_sum / 20;
            -- 向所有记录的FIVE列插入20天收盘价的平均值
            update commodity_future_date_data cfdd
               set cfdd.ma20 = round(v_twenty_average, 2)
             where cfdd.code = v_code
               and cfdd.transaction_date = j.transaction_date;
            -- dbms_output.put_line('v_twenty_average'||'  :  '||v_twenty_average);
          end if;
        end loop;
      end loop;
      commit;
    end;
  end CALCULATE_TWENTY;
  -- end procedure

  /*---------------------------------------------------- calculate sixty moving average -------------------------------------------------*/
  procedure CALCULATE_SIXTY is
  begin
    ------------------------------------------ standard declare section ---------------------------------------------------
    declare
      -- 表示code
      v_code varchar2(10);
      -- 表示60天收盘价的和
      v_sixty_sum number := 0;
      -- 表示60天收盘价的平均值
      v_sixty_average number := 0;
      -- 定义一个含有60个数值型数据的数组
      type type_array is varray(60) of number;
      array_sixty type_array := type_array();
      -- define cursor section.返回全部code
      cursor cur_all_code is
        select distinct cfdd.code from commodity_future_date_data cfdd;
      cursor cur_all_close_price is
        select cfdd.close_price, cfdd.transaction_date
          from commodity_future_date_data cfdd
         where cfdd.code = v_code
         order by cfdd.transaction_date asc;
      ------------------------------------------ standard declare section ---------------------------------------------------
    
    begin
      --------------------------------- standard for loop section --------------------------------------------------------
      for i in cur_all_code loop
        v_code := i.code;
        -- dbms_output.put_line('**************   v_code'||'  :  '||v_code);
        array_sixty := type_array();
        for j in cur_all_close_price loop
          array_sixty.extend; -- 扩展数组，扩展一个元素
          array_sixty(array_sixty.count) := j.close_price;
          if mod(array_sixty.count, 60) = 0 then
            -- dbms_output.put_line('j.date_'||'  :  '||j.date_);
            v_sixty_sum := 0;
            for x in 1 .. array_sixty.count loop
              -- 求60天收盘价的和
              v_sixty_sum := v_sixty_sum + array_sixty(x);
            end loop;
            -- dbms_output.put_line('v_sixty_sum'||'  :  '||v_sixty_sum);
            -- 删除数组中的第一个元素，将其与59个元素向前挪一位，并删除下标为60的元素
            for y in 1 .. array_sixty.count - 1 loop
              array_sixty(y) := array_sixty(y + 1);
            end loop;
            array_sixty.trim;
            -- 60天收盘价的平均值
            v_sixty_average := v_sixty_sum / 60;
            -- 向所有记录的FIVE列插入60天收盘价的平均值
            update commodity_future_date_data cfdd
               set cfdd.ma60 = round(v_sixty_average, 2)
             where cfdd.code = v_code
               and cfdd.transaction_date = j.transaction_date;
            -- dbms_output.put_line('v_sixty_average'||'  :  '||v_sixty_average);
          end if;
        end loop;
      end loop;
      commit;
    end;
  end CALCULATE_SIXTY;
  -- end procedure

  /*---------------------------------------------------- calculate one hundred twenty moving average -------------------------------------------------*/
  procedure CALCULATE_ONEHUNDREDTWENTY is
  begin
    ------------------------------------------ standard declare section ---------------------------------------------------
    declare
      -- 表示code
      v_code varchar2(10);
      -- 表示120天收盘价的和
      v_one_hundred_twenty_sum number := 0;
      -- 表示120天收盘价的平均值
      v_one_hundred_twenty_average number := 0;
      -- 定义一个含有120个数值型数据的数组
      type type_array is varray(120) of number;
      array_one_hundred_twenty type_array := type_array();
      -- define cursor section.返回全部code
      cursor cur_all_code is
        select distinct cfdd.code from commodity_future_date_data cfdd;
      cursor cur_all_close_price is
        select cfdd.close_price, cfdd.transaction_date
          from commodity_future_date_data cfdd
         where cfdd.code = v_code
         order by cfdd.transaction_date asc;
      ------------------------------------------ standard declare section ---------------------------------------------------
    
    begin
      --------------------------------- standard for loop section --------------------------------------------------------
      for i in cur_all_code loop
        v_code := i.code;
        -- dbms_output.put_line('**************   v_code'||'  :  '||v_code);
        array_one_hundred_twenty := type_array();
        for j in cur_all_close_price loop
          array_one_hundred_twenty.extend; -- 扩展数组，扩展一个元素
          array_one_hundred_twenty(array_one_hundred_twenty.count) := j.close_price;
          if mod(array_one_hundred_twenty.count, 120) = 0 then
            -- dbms_output.put_line('j.date_'||'  :  '||j.date_);
            v_one_hundred_twenty_sum := 0;
            for x in 1 .. array_one_hundred_twenty.count loop
              -- 求120天收盘价的和
              v_one_hundred_twenty_sum := v_one_hundred_twenty_sum +
                                          array_one_hundred_twenty(x);
            end loop;
            -- dbms_output.put_line('v_one_hundred_twenty_sum'||'  :  '||v_one_hundred_twenty_sum);
            -- 删除数组中的第一个元素，将其与119个元素向前挪一位，并删除下标为120的元素
            for y in 1 .. array_one_hundred_twenty.count - 1 loop
              array_one_hundred_twenty(y) := array_one_hundred_twenty(y + 1);
            end loop;
            array_one_hundred_twenty.trim;
            -- 120天收盘价的平均值
            v_one_hundred_twenty_average := v_one_hundred_twenty_sum / 120;
            -- 向所有记录的FIVE列插入120天收盘价的平均值
            update commodity_future_date_data cfdd
               set cfdd.ma120 = round(v_one_hundred_twenty_average, 2)
             where cfdd.code = v_code
               and cfdd.transaction_date = j.transaction_date;
            -- dbms_output.put_line('v_one_hundred_twenty_average'||'  :  '||v_one_hundred_twenty_average);
          end if;
        end loop;
      end loop;
      commit;
    end;
  end CALCULATE_ONEHUNDREDTWENTY;
  -- end procedure

  /*---------------------------------------------------- calculate one hundred fifty moving average -------------------------------------------------*/
  procedure CALCULATE_TWOHUNDREDFIFTY is
  begin
    ------------------------------------------ standard declare section ---------------------------------------------------
    declare
      -- 表示code
      v_code varchar2(10);
      -- 表示250天收盘价的和
      v_one_hundred_fifty_sum number := 0;
      -- 表示250天收盘价的平均值
      v_one_hundred_fifty_average number := 0;
      -- 定义一个含有250个数值型数据的数组
      type type_array is varray(250) of number;
      array_one_hundred_fifty type_array := type_array();
      -- define cursor section.返回全部stock_code
      cursor cur_all_code is
        select distinct cfdd.code from commodity_future_date_data cfdd;
      cursor cur_all_close_price is
        select cfdd.close_price, cfdd.transaction_date
          from commodity_future_date_data cfdd
         where cfdd.code = v_code
         order by cfdd.transaction_date asc;
      ------------------------------------------ standard declare section ---------------------------------------------------
    
    begin
      --------------------------------- standard for loop section --------------------------------------------------------
      for i in cur_all_code loop
        v_code := i.code;
        -- dbms_output.put_line('**************   v_code'||'  :  '||v_code);
        array_one_hundred_fifty := type_array();
        for j in cur_all_close_price loop
          array_one_hundred_fifty.extend; -- 扩展数组，扩展一个元素
          array_one_hundred_fifty(array_one_hundred_fifty.count) := j.close_price;
          if mod(array_one_hundred_fifty.count, 250) = 0 then
            -- dbms_output.put_line('j.date_'||'  :  '||j.date_);
            v_one_hundred_fifty_sum := 0;
            for x in 1 .. array_one_hundred_fifty.count loop
              -- 求250天收盘价的和
              v_one_hundred_fifty_sum := v_one_hundred_fifty_sum +
                                         array_one_hundred_fifty(x);
            end loop;
            -- dbms_output.put_line('v_one_hundred_fifty_sum'||'  :  '||v_one_hundred_fifty_sum);
            -- 删除数组中的第一个元素，将其与249个元素向前挪一位，并删除下标为250的元素
            for y in 1 .. array_one_hundred_fifty.count - 1 loop
              array_one_hundred_fifty(y) := array_one_hundred_fifty(y + 1);
            end loop;
            array_one_hundred_fifty.trim;
            -- 250天收盘价的平均值
            v_one_hundred_fifty_average := v_one_hundred_fifty_sum / 250;
            -- 向所有记录的FIVE列插入250天收盘价的平均值
            update commodity_future_date_data cfdd
               set cfdd.ma250 = round(v_one_hundred_fifty_average, 2)
             where cfdd.code = v_code
               and cfdd.transaction_date = j.transaction_date;
            -- dbms_output.put_line('v_one_hundred_fifty_average'||'  :  '||v_one_hundred_fifty_average);
          end if;
        end loop;
      end loop;
      commit;
    end;
  end CALCULATE_TWOHUNDREDFIFTY;

  /*---------------------------------------------------- write moving average by date -------------------------------------------------*/
  procedure WRITE_MOVING_AVERAGE_BY_DATE(p_date in varchar2) is
    -- 表示code
    v_code varchar2(10);
    -- 表示5天收盘价的平均值
    v_five_average number := 0;
    -- 表示10天收盘价的平均值
    v_ten_average number := 0;
    -- 表示20天收盘价的平均值
    v_twenty_average number := 0;
    -- 表示60天收盘价的平均值
    v_sixty_average number := 0;
    -- 表示120天收盘价的平均值
    v_one_hundred_twenty_average number := 0;
    -- 表示250天收盘价的平均值
    v_two_hundred_fifty_average number := 0;
    -- 表示所有天收盘价的平均值
    --infiniteAverage number:=0;
    -- 用于判断某只期货的记录数是否可以计算均线
    v_average_num number;
    -- 计算某只期货，在某个交易日之前的所有交易记录
    cursor cur_row_num_by_date is
      select t.code, count(*) row_num
        from commodity_future_date_data t
       where t.transaction_date <= to_date(p_date, 'yyyy-mm-dd')
       group by t.code;
  begin
    for i in cur_row_num_by_date loop
      v_code        := i.code;
      v_average_num := i.row_num;
    
      /*select
             (select avg(stock_close) from(select * from stock_moving_average t where t.stock_code=stockCode and t.stock_date<=to_date(stockDate,'yyyy-mm-dd') and rownum<=5 order by t.stock_date desc)),
             (select avg(stock_close) from(select * from stock_moving_average t where t.stock_code=stockCode and t.stock_date<=to_date(stockDate,'yyyy-mm-dd') and rownum<=10 order by t.stock_date desc)),
             (select avg(stock_close) from(select * from stock_moving_average t where t.stock_code=stockCode and t.stock_date<=to_date(stockDate,'yyyy-mm-dd') and rownum<=20 order by t.stock_date desc)),
             (select avg(stock_close) from(select * from stock_moving_average t where t.stock_code=stockCode and t.stock_date<=to_date(stockDate,'yyyy-mm-dd') and rownum<=60 order by t.stock_date desc)),
             (select avg(stock_close) from(select * from stock_moving_average t where t.stock_code=stockCode and t.stock_date<=to_date(stockDate,'yyyy-mm-dd') and rownum<=120 order by t.stock_date desc)),
             (select avg(stock_close) from(select * from stock_moving_average t where t.stock_code=stockCode and t.stock_date<=to_date(stockDate,'yyyy-mm-dd') and rownum<=250 order by t.stock_date desc))
             into fiveAverage,tenAverage,twentyAverage,sixtyAverage,oneHundredTwentyAverage,twoHundredFiftyAverage
      from dual;
      update stock_moving_average t
      set t.five=round(fiveAverage,2),t.ten=round(tenAverage,2),t.twenty=round(twentyAverage,2),
          t.sixty=round(sixtyAverage,2),t.one_hundred_twenty=round(oneHundredTwentyAverage,2),
          t.two_hundred_fifty=round(twoHundredFiftyAverage,2)
      where t.stock_code=stockCode and t.stock_date=to_date(stockDate,'yyyy-mm-dd');
      commit;*/
    
      -- 每只期货都重置如下变量
      v_five_average               := null;
      v_ten_average                := null;
      v_twenty_average             := null;
      v_sixty_average              := null;
      v_one_hundred_twenty_average := null;
      v_two_hundred_fifty_average  := null;
    
      -- 更新所有期货某一天的5日均线
      if v_average_num >= 5 then
        select avg(d.close_price)
          into v_five_average
          from (select *
                  from commodity_future_date_data t
                 where t.code = v_code
                   and t.transaction_date <= to_date(p_date, 'yyyy-mm-dd')
                 order by t.transaction_date desc) d
         where rownum <= 5;
      end if;
    
      -- 更新所有期货某一天的10日均线
      if v_average_num >= 10 then
        select avg(d.close_price)
          into v_ten_average
          from (select *
                  from commodity_future_date_data t
                 where t.code = v_code
                   and t.transaction_date <= to_date(p_date, 'yyyy-mm-dd')
                 order by t.transaction_date desc) d
         where rownum <= 10;
      end if;
    
      -- 更新所有期货某一天的20日均线
      if v_average_num >= 20 then
        select avg(d.close_price)
          into v_twenty_average
          from (select *
                  from commodity_future_date_data t
                 where t.code = v_code
                   and t.transaction_date <= to_date(p_date, 'yyyy-mm-dd')
                 order by t.transaction_date desc) d
         where rownum <= 20;
      end if;
    
      -- 更新所有期货某一天的60日均线
      if v_average_num >= 60 then
        select avg(d.close_price)
          into v_sixty_average
          from (select *
                  from commodity_future_date_data t
                 where t.code = v_code
                   and t.transaction_date <= to_date(p_date, 'yyyy-mm-dd')
                 order by t.transaction_date desc) d
         where rownum <= 60;
      end if;
    
      -- 更新所有期货某一天的120日均线
      if v_average_num >= 120 then
        select avg(d.close_price)
          into v_one_hundred_twenty_average
          from (select *
                  from commodity_future_date_data t
                 where t.code = v_code
                   and t.transaction_date <= to_date(p_date, 'yyyy-mm-dd')
                 order by t.transaction_date desc) d
         where rownum <= 120;
      end if;
    
      -- 更新所有期货某一天的250日均线
      if v_average_num >= 250 then
        select avg(d.close_price)
          into v_two_hundred_fifty_average
          from (select *
                  from commodity_future_date_data t
                 where t.code = v_code
                   and t.transaction_date <= to_date(p_date, 'yyyy-mm-dd')
                 order by t.transaction_date desc) d
         where rownum <= 250;
      end if;
    
      update commodity_future_date_data t
         set t.ma5   = nvl2(v_five_average, round(v_five_average, 2), null),
             t.ma10  = nvl2(v_ten_average, round(v_ten_average, 2), null),
             t.ma20  = nvl2(v_twenty_average,
                            round(v_twenty_average, 2),
                            null),
             t.ma60  = nvl2(v_sixty_average, round(v_sixty_average, 2), null),
             t.ma120 = nvl2(v_one_hundred_twenty_average,
                            round(v_one_hundred_twenty_average, 2),
                            null),
             t.ma250 = nvl2(v_two_hundred_fifty_average,
                            round(v_two_hundred_fifty_average, 2),
                            null)
       where t.code = v_code
         and t.transaction_date = to_date(p_date, 'yyyy-mm-dd');
    
    -- 更新所有期货某一天的所有日均线
    /*select avg(d.stock_close) into infiniteAverage from(
                                                                                                                                                                                                                                                                                                                                                                                                                 select * from stock_moving_average t where t.stock_code=stockCode and t.stock_date<=to_date(stockDate,'yyyy-mm-dd') order by t.stock_date desc
                                                                                                                                                                                                                                                                                                                                                                                                          ) d;
                                                                                                                                                                                                                                                                                                                                                                                                          update stock_moving_average t set t.infinite=round(infiniteAverage,2) where t.stock_code=stockCode and t.stock_date=to_date(stockDate,'yyyy-mm-dd');
                                                                                                                                                                                                                                                                                                                                                                                                          */
    end loop;
    commit;
  end WRITE_MOVING_AVERAGE_BY_DATE;

  /*------------------------------------------------------ write all MACD ------------------------------------------------------*/
  procedure WRITE_MACD_INIT as
    -- 表示code
    v_code varchar2(10);
    -- define cursor section.返回全部code
    cursor cur_all_code is
      select distinct t.code
        from commodity_future_date_data t
       order by t.code asc;
    -- 获取每只期货第一个交易日的日期
    cursor cur_first_date is
      select min(t.transaction_date) as transaction_date
        from commodity_future_date_data t
       where t.code = v_code;
  begin
    -- 初始化每只期货第一个交易日的ema12,ema26,dif和dea字段
    for i in cur_all_code loop
      v_code := i.code;
      for j in cur_first_date loop
        update commodity_future_date_data t
           set t.ema12 = t.close_price,
               t.ema26 = t.close_price,
               t.dif   = 0,
               t.dea   = 0
         where t.code = v_code
           and t.transaction_date = j.transaction_date;
        commit;
      end loop;
    end loop;
  end WRITE_MACD_INIT;

  procedure WRITE_MACD_EMA as
    v_pre_ema12  number;
    v_pre_ema26  number;
    v_pre_dif    number;
    v_pre_dea    number;
    v_first_date date;
    -- 表示code
    v_code varchar2(10);
    -- define cursor section.返回全部code
    cursor cur_all_code is
      select distinct t.code
        from commodity_future_date_data t
       order by t.code asc;
    -- 获取每只期货第一个交易日的日期
    cursor cur_first_date is
      select min(t.transaction_date) as transaction_date
        from commodity_future_date_data t
       where t.code = v_code;
    -- 根据v_code选出某只期货的除第一天以外的全部交易记录，按升序排列
    cursor cur_all is
      select distinct *
        from commodity_future_date_data t
       where t.code = v_code
         and t.transaction_date > v_first_date
       order by t.transaction_date asc;
  begin
    -- 计算每只期货其余交易日的ema12,ema26,dif和dea字段
    for i in cur_all_code loop
      v_code := i.code;
      -- 用记录是第一个交易日的字段初始化相关变量
      for x in cur_first_date loop
        select t.ema12, t.ema26, t.dif, t.dea
          into v_pre_ema12, v_pre_ema26, v_pre_dif, v_pre_dea
          from commodity_future_date_data t
         where t.code = v_code
           and t.transaction_date = x.transaction_date;
      end loop;
    
      select min(t.transaction_date)
        into v_first_date
        from commodity_future_date_data t
       where t.code = v_code;
      for j in cur_all loop
        -- 对于其余交易日，更新ema12,ema26,dif和dea字段
        update commodity_future_date_data t
           set t.ema12 = v_pre_ema12 * 11 / 13 + j.close_price * 2 / 13,
               t.ema26 = v_pre_ema26 * 25 / 27 + j.close_price * 2 / 27
         where t.code = v_code
           and t.transaction_date = j.transaction_date;
        commit;
        -- 用于计算下一个交易日时使用
        select t.ema12
          into v_pre_ema12
          from commodity_future_date_data t
         where t.code = v_code
           and t.transaction_date = j.transaction_date;
        select t.ema26
          into v_pre_ema26
          from commodity_future_date_data t
         where t.code = v_code
           and t.transaction_date = j.transaction_date;
      end loop;
    end loop;
  end WRITE_MACD_EMA;

  procedure WRITE_MACD_DIF as
    v_pre_ema12  number;
    v_pre_ema26  number;
    v_pre_dif    number;
    v_pre_dea    number;
    v_first_date date;
    -- 表示code
    v_code varchar2(10);
    -- define cursor section.返回全部code
    cursor cur_all_code is
      select distinct t.code
        from commodity_future_date_data t
       order by t.code asc;
    -- 获取每只期货第一个交易日的日期
    cursor cur_first_date is
      select min(t.transaction_date) as transaction_date
        from commodity_future_date_data t
       where t.code = v_code;
    -- 根据v_code选出某只期货的除第一天以外的全部交易记录，按升序排列
    cursor cur_all is
      select distinct *
        from commodity_future_date_data t
       where t.code = v_code
         and t.transaction_date > v_first_date
       order by t.transaction_date asc;
  begin
    -- 计算每只期货其余交易日的ema12,ema26,dif和dea字段
    for i in cur_all_code loop
      v_code := i.code;
      -- 用记录是第一个交易日的字段初始化相关变量
      for x in cur_first_date loop
        select t.ema12, t.ema26, t.dif, t.dea
          into v_pre_ema12, v_pre_ema26, v_pre_dif, v_pre_dea
          from commodity_future_date_data t
         where t.code = v_code
           and t.transaction_date = x.transaction_date;
      end loop;
      select min(t.transaction_date)
        into v_first_date
        from commodity_future_date_data t
       where t.code = v_code;
      for j in cur_all loop
        update commodity_future_date_data t
           set t.dif = t.ema12 - t.ema26
         where t.code = v_code
           and t.transaction_date = j.transaction_date;
        commit;
        select t.dif
          into v_pre_dif
          from commodity_future_date_data t
         where t.code = v_code
           and t.transaction_date = j.transaction_date;
      end loop;
    end loop;
  end WRITE_MACD_DIF;

  procedure WRITE_MACD_DEA as
    v_pre_ema12  number;
    v_pre_ema26  number;
    v_pre_dif    number;
    v_pre_dea    number;
    v_first_date date;
    -- 表示code
    v_code varchar2(10);
    -- define cursor section.返回全部code
    cursor cur_all_code is
      select distinct t.code
        from commodity_future_date_data t
       order by t.code asc;
    -- 获取每只期货第一个交易日的日期
    cursor cur_first_date is
      select min(t.transaction_date) as transaction_date
        from commodity_future_date_data t
       where t.code = v_code;
    -- 根据v_code选出某只期货的除第一天以外的全部交易记录，按升序排列
    cursor cur_all is
      select distinct *
        from commodity_future_date_data t
       where t.code = v_code
         and t.transaction_date > v_first_date
       order by t.transaction_date asc;
  begin
    -- 计算每只期货其余交易日的ema12,ema26,dif和dea字段
    for i in cur_all_code loop
      v_code := i.code;
      -- 用记录是第一个交易日的字段初始化相关变量
      for x in cur_first_date loop
        select t.ema12, t.ema26, t.dif, t.dea
          into v_pre_ema12, v_pre_ema26, v_pre_dif, v_pre_dea
          from commodity_future_date_data t
         where t.code = v_code
           and t.transaction_date = x.transaction_date;
      end loop;
      select min(t.transaction_date)
        into v_first_date
        from commodity_future_date_data t
       where t.code = v_code;
      for j in cur_all loop
        update commodity_future_date_data t
           set t.dea = v_pre_dea * 8 / 10 + t.dif * 2 / 10
         where t.code = v_code
           and t.transaction_date = j.transaction_date;
        commit;
        select t.dea
          into v_pre_dea
          from commodity_future_date_data t
         where t.code = v_code
           and t.transaction_date = j.transaction_date;
      end loop;
    end loop;
  end WRITE_MACD_DEA;

  procedure WRITE_MACD as
  begin
    update commodity_future_date_data t
       set t.macd = 2 * (t.dif - t.dea)
     where t.dif is not null
       and t.dea is not null;
    commit;
  end WRITE_MACD;

  /*-------------------------------- write all MACD by date -----------------------------------------------*/
  procedure WRITE_MACD_EMA_BY_DATE(p_date in varchar2) as
    v_pre_ema12 number;
    v_pre_ema26 number;
    v_pre_dea   number;
    -- 表示code
    v_code varchar2(10);
    -- define cursor section.返回全部code
    cursor cur_all_code is
      select distinct t.code
        from commodity_future_date_data t
       order by t.code asc;
    -- 获取某只期货最近两天的记录
    cursor cur_single is
      select *
        from (select *
                from commodity_future_date_data t
               where t.code = v_code
                 and t.transaction_date <= to_date(p_date, 'yyyy-mm-dd')
               order by t.transaction_date desc)
       where rownum <= 2;
  begin
    for i in cur_all_code loop
      v_code := i.code;
      for j in cur_single loop
        if j.transaction_date != to_date(p_date, 'yyyy-mm-dd') then
          v_pre_ema12 := j.ema12;
          v_pre_ema26 := j.ema26;
          v_pre_dea   := j.dea;
          update commodity_future_date_data t
             set t.ema12 = v_pre_ema12 * 11 / 13 + t.close_price * 2 / 13,
                 t.ema26 = v_pre_ema26 * 25 / 27 + t.close_price * 2 / 27
           where t.code = v_code
             and t.transaction_date = to_date(p_date, 'yyyy-mm-dd');
          commit;
        end if;
      end loop;
    end loop;
  end WRITE_MACD_EMA_BY_DATE;

  procedure WRITE_MACD_DIF_BY_DATE(p_date in varchar2) as
    v_pre_ema12 number;
    v_pre_ema26 number;
    v_pre_dea   number;
    -- 表示code
    v_code varchar2(10);
    -- define cursor section.返回全部code
    cursor cur_all_code is
      select distinct t.code
        from commodity_future_date_data t
       order by t.code asc;
    -- 获取某只期货最近两天的记录
    cursor cur_single is
      select *
        from (select *
                from commodity_future_date_data t
               where t.code = v_code
                 and t.transaction_date <= to_date(p_date, 'yyyy-mm-dd')
               order by t.transaction_date desc)
       where rownum <= 2;
  begin
    for i in cur_all_code loop
      v_code := i.code;
      for j in cur_single loop
        if j.transaction_date != to_date(p_date, 'yyyy-mm-dd') then
          v_pre_ema12 := j.ema12;
          v_pre_ema26 := j.ema26;
          v_pre_dea   := j.dea;
          update commodity_future_date_data t
             set t.dif = t.ema12 - t.ema26
           where t.code = v_code
             and t.transaction_date = to_date(p_date, 'yyyy-mm-dd');
          commit;
        end if;
      end loop;
    end loop;
  end WRITE_MACD_DIF_BY_DATE;

  procedure WRITE_MACD_DEA_BY_DATE(p_date in varchar2) as
    v_pre_ema12 number;
    v_pre_ema26 number;
    v_pre_dea   number;
    -- 表示code
    v_code varchar2(10);
    -- define cursor section.返回全部code
    cursor cur_all_code is
      select distinct t.code
        from commodity_future_date_data t
       order by t.code asc;
    -- 获取某只期货最近两天的记录
    cursor cur_single is
      select *
        from (select *
                from commodity_future_date_data t
               where t.code = v_code
                 and t.transaction_date <= to_date(p_date, 'yyyy-mm-dd')
               order by t.transaction_date desc)
       where rownum <= 2;
  begin
    for i in cur_all_code loop
      v_code := i.code;
      for j in cur_single loop
        if j.transaction_date != to_date(p_date, 'yyyy-mm-dd') then
          v_pre_ema12 := j.ema12;
          v_pre_ema26 := j.ema26;
          v_pre_dea   := j.dea;
          update commodity_future_date_data t
             set t.dea = v_pre_dea * 8 / 10 + t.dif * 2 / 10
           where t.code = v_code
             and t.transaction_date = to_date(p_date, 'yyyy-mm-dd');
          commit;
        end if;
      end loop;
    end loop;
  end WRITE_MACD_DEA_BY_DATE;

  procedure WRITE_MACD_BY_DATE(p_date in varchar2) as
    -- 表示code
    v_code varchar2(10);
    -- define cursor section.返回全部code
    cursor cur_all_code is
      select distinct t.code
        from commodity_future_date_data t
       order by t.code asc;
  begin
    for i in cur_all_code loop
      v_code := i.code;
      update commodity_future_date_data t
         set t.macd = 2 * (t.dif - t.dea)
       where t.dif is not null
         and t.dea is not null
         and t.code = v_code
         and t.transaction_date = to_date(p_date, 'yyyy-mm-dd');
    end loop;
    commit;
  end WRITE_MACD_BY_DATE;

  /*--------------------------------- 计算日线级别所有期货的KD指标 ----------------------------*/
  procedure WRITE_KD_INIT as
    -- 表示某只期货的code字段
    v_code varchar2(10);
    -- 用于计算是否是第9个交易周
    num number;
    -- define cursor section.返回全部code
    cursor cur_all_code is
      select distinct t.code
        from commodity_future_date_data t
       order by t.code asc;
    -- 查询某只期货最初的8个交易周的记录，并按生序排列
    cursor cur_single_k is
      select *
        from (select *
                from commodity_future_date_data t
               where t.code = v_code
               order by t.transaction_date asc)
       where rownum <= 8;
  begin
    -- 初始化每只期货第一个交易日的K和D字段
    for i in cur_all_code loop
      v_code := i.code;
      num    := 0;
      for j in cur_single_k loop
        num := num + 1;
        if num = 8 then
          -- 若无前一日K值与D值，则可分别用50来代替
          update commodity_future_date_data t
             set t.k = 50, t.d = 50
           where t.code = v_code
             and t.transaction_date = j.transaction_date;
          commit;
        end if;
      end loop;
    end loop;
  end WRITE_KD_INIT;

  procedure WRITE_KD_RSV as
    -- 表示某只期货的code字段
    v_code varchar2(10);
    -- 9日内最高价
    v_nine_day_highest_price number;
    -- 9日内最低价
    v_nine_day_lowest_price number;
    -- 计算指标时使用，用于表示日期的累积
    num number;
    -- define cursor section.返回全部code
    cursor cur_all_code is
      select distinct t.code
        from commodity_future_date_data t
       order by t.code asc;
    -- 获取某只期货所有的日线级别的交易记录，并按升序排列
    cursor cur_single is
      select *
        from commodity_future_date_data t
       where t.code = v_code
       order by t.transaction_date asc;
  begin
    for i in cur_all_code loop
      v_code := i.code;
      num    := 0;
      for j in cur_single loop
        num := num + 1;
        if num >= 9 then
          -- 计算9日内最高价和最低价
          select max(highest_price), min(lowest_price)
            into v_nine_day_highest_price, v_nine_day_lowest_price
            from (select *
                    from commodity_future_date_data t
                   where t.code = v_code
                     and t.transaction_date <= j.transaction_date
                   order by t.transaction_date desc)
           where rownum <= 9;
        
          -- 计算rsv
          if (v_nine_day_highest_price - v_nine_day_lowest_price) = 0 then
            update commodity_future_date_data t
               set t.rsv =
                   (select t1.rsv
                      from (select *
                              from commodity_future_date_data t
                             where t.code = v_code
                               and t.transaction_date < j.transaction_date
                             order by t.transaction_date desc) t1
                     where rownum <= 1)
             where t.code = v_code
               and t.transaction_date = j.transaction_date;
          else
            update commodity_future_date_data t
               set t.rsv =
                   (t.close_price - v_nine_day_lowest_price) /
                   (v_nine_day_highest_price - v_nine_day_lowest_price) * 100
             where t.code = v_code
               and t.transaction_date = j.transaction_date;
          end if;
          commit;
        end if;
      end loop;
    end loop;
  end WRITE_KD_RSV;

  procedure WRITE_KD_K as
    -- 表示某只期货的CODE字段
    v_code varchar2(10);
    -- 计算指标时使用，用于表示日期的累积
    num number;
    -- 表示KD指标的K
    v_temp_k number;
    -- define cursor section.返回全部code
    cursor cur_all_code is
      select distinct t.code
        from commodity_future_date_data t
       order by t.code asc;
    -- 获取某只期货所有的日线级别的交易记录，并按升序排列
    cursor cur_single is
      select *
        from commodity_future_date_data t
       where t.code = v_code
       order by t.transaction_date asc;
  begin
    for i in cur_all_code loop
      v_code := i.code;
      num    := 0;
      for j in cur_single loop
        num := num + 1;
        if num = 8 then
          v_temp_k := j.k;
        end if;
        if num >= 9 then
          -- 计算K
          update commodity_future_date_data t
             set t.k = 2 / 3 * v_temp_k + 1 / 3 * t.rsv
           where t.code = v_code
             and t.transaction_date = j.transaction_date;
          commit;
          select t.k
            into v_temp_k
            from commodity_future_date_data t
           where t.code = v_code
             and t.transaction_date = j.transaction_date;
        end if;
      
      end loop;
    end loop;
  end WRITE_KD_K;

  procedure WRITE_KD_D as
    -- 表示某只期货的code字段
    v_code varchar2(10);
    -- 计算指标时使用，用于表示日期的累积
    num number;
    -- 表示KD指标的D
    v_temp_d number;
    -- define cursor section.返回全部code
    cursor cur_all_code is
      select distinct t.code
        from commodity_future_date_data t
       order by t.code asc;
    -- 获取某只期货所有的日线级别的交易记录，并按升序排列
    cursor cur_single is
      select *
        from commodity_future_date_data t
       where t.code = v_code
       order by t.transaction_date asc;
  begin
    for i in cur_all_code loop
      v_code := i.code;
      num    := 0;
      for j in cur_single loop
        num := num + 1;
        if num = 8 then
          v_temp_d := j.d;
        end if;
        if num >= 9 then
          -- 计算D
          update commodity_future_date_data t
             set t.d = 2 / 3 * v_temp_d + 1 / 3 * j.k
           where t.code = v_code
             and t.transaction_date = j.transaction_date;
          commit;
          select t.d
            into v_temp_d
            from commodity_future_date_data t
           where t.code = v_code
             and t.transaction_date = j.transaction_date;
        end if;
      end loop;
    end loop;
  end WRITE_KD_D;

  ---------------------------------- 计算日线级别，某一日，所有期货的KD指标 ----------------------------
  procedure WRITE_KD_BY_DATE_RSV(p_date varchar2) as
    -- 表示某只期货的code字段
    v_code varchar2(10);
    -- 9日内最高价
    v_nine_day_highest_price number;
    -- 9日内最低价
    v_nine_day_lowest_price number;
    -- define cursor section.返回全部code
    cursor cur_all_code is
      select distinct t.code
        from commodity_future_date_data t
       order by t.code asc;
  begin
    for i in cur_all_code loop
      v_code := i.code;
      -- 计算9日内最高价和最低价
      select max(highest_price), min(lowest_price)
        into v_nine_day_highest_price, v_nine_day_lowest_price
        from (select *
                from commodity_future_date_data t
               where t.code = v_code
                 and t.transaction_date <= to_date(p_date, 'yyyy-mm-dd')
               order by t.transaction_date desc)
       where rownum <= 9;
      -- 计算某只期货某一日的RSV
      if (v_nine_day_highest_price - v_nine_day_lowest_price) = 0 then
        update commodity_future_date_data t
           set t.rsv =
               (select t1.rsv
                  from (select *
                          from commodity_future_date_data t
                         where t.code = v_code
                           and t.transaction_date <
                               to_date(p_date, 'yyyy-mm-dd')
                         order by t.transaction_date desc) t1
                 where rownum <= 1)
         where t.code = v_code
           and t.transaction_date = to_date(p_date, 'yyyy-mm-dd');
      else
        update commodity_future_date_data t
           set t.rsv =
               (t.close_price - v_nine_day_lowest_price) /
               (v_nine_day_highest_price - v_nine_day_lowest_price) * 100
         where t.code = v_code
           and t.transaction_date = to_date(p_date, 'yyyy-mm-dd');
      end if;
      commit;
    end loop;
  end WRITE_KD_BY_DATE_RSV;

  procedure WRITE_KD_BY_DATE_K(p_date varchar2) as
    -- 表示某只期货的code字段
    v_code varchar2(10);
    -- 表示前一日K值
    v_temp_k number;
    -- 用于计数
    v_num number;
    -- define cursor section.返回全部code
    cursor cur_all_code is
      select distinct t.code
        from commodity_future_date_data t
       order by t.code asc;
    -- 按照日期参数，获取某只期货最近两天的日线级别的交易记录，并按降序排列
    cursor cur_single is
      select *
        from (select *
                from commodity_future_date_data t
               where t.code = v_code
                 and t.transaction_date <= to_date(p_date, 'yyyy-mm-dd')
               order by t.transaction_date desc)
       where rownum <= 2
       order by transaction_date asc;
  begin
    for i in cur_all_code loop
      v_code := i.code;
      v_num  := 0;
      for j in cur_single loop
        v_num := v_num + 1;
        if v_num = 1 then
          v_temp_k := j.k;
        end if;
        if v_num = 2 then
          update commodity_future_date_data t
             set t.k = 2 / 3 * v_temp_k + 1 / 3 * t.rsv
           where t.code = v_code
             and t.transaction_date = to_date(p_date, 'yyyy-mm-dd');
          commit;
        end if;
      end loop;
    end loop;
  end WRITE_KD_BY_DATE_K;

  procedure WRITE_KD_BY_DATE_D(p_date varchar2) as
    -- 表示某只期货的code字段
    v_code varchar2(10);
    -- 表示前一日K值
    v_temp_d number;
    -- 用于计数
    v_num number;
    -- define cursor section.返回全部code
    cursor cur_all_code is
      select distinct t.code
        from commodity_future_date_data t
       order by t.code asc;
    -- 按照日期参数，获取某只期货最近两天的日线级别的交易记录，并按降序排列
    cursor cur_single is
      select *
        from (select *
                from commodity_future_date_data t
               where t.code = v_code
                 and t.transaction_date <= to_date(p_date, 'yyyy-mm-dd')
               order by t.transaction_date desc)
       where rownum <= 2
       order by transaction_date asc;
  begin
    for i in cur_all_code loop
      v_code := i.code;
      v_num  := 0;
      for j in cur_single loop
        v_num := v_num + 1;
        if v_num = 1 then
          v_temp_d := j.d;
        end if;
        if v_num = 2 then
          update commodity_future_date_data t
             set t.d = 2 / 3 * v_temp_d + 1 / 3 * t.k
           where t.code = v_code
             and t.transaction_date = to_date(p_date, 'yyyy-mm-dd');
          commit;
        end if;
      end loop;
    end loop;
  end WRITE_KD_BY_DATE_D;

  /*------------------------------ 计算所有期货的ha -----------------------------------------*/
  procedure WRITE_HA as
    -- 代码
    v_code varchar2(10);
    -- 日期
    v_date date;
    -- 获取所有的CODE
    cursor cur_all_code is
      select distinct t.code code from commodity_future_date_data t;
    -- 查询某个具体的期货的第一条交易记录
    cursor cur_fist_cfdd is
      select *
        from (select *
                from commodity_future_date_data t
               where t.code = v_code
               order by t.transaction_date asc)
       where rownum <= 1;
    -- 查询某个期货除了最早的一条记录外的其他记录，并按升序排列
    cursor cur_later_cfdd is
      select *
        from commodity_future_date_data t
       where t.code = v_code
         and t.transaction_date > v_date
       order by t.transaction_date asc;
    -- 定义表commodity_future_date_data结构的游标变量
    first_cfdd commodity_future_date_data%rowtype;
    later_cfdd commodity_future_date_data%rowtype;
    pre_cfdd   commodity_future_date_data%rowtype;
    -- 用于计算hei kin ashi平均K线开盘价，收盘价，最高价和最低价的变量
    v_ha_open_price    number;
    v_ha_close_price   number;
    v_ha_highest_price number;
    v_ha_lowest_price  number;
  begin
    open cur_all_code;
    loop
      -- 获取每个期货的code字段
      fetch cur_all_code
        into v_code;
      exit when cur_all_code%notfound;
    
      -- 先计算每个期货的第一条记录
      open cur_fist_cfdd;
      fetch cur_fist_cfdd
        into first_cfdd;
      exit when cur_fist_cfdd%notfound;
    
      -- 计算hei kin ashi平均K线开盘价，收盘价，最高价和最低价
      v_ha_open_price  := (first_cfdd.open_price + first_cfdd.close_price) / 2;
      v_ha_close_price := (first_cfdd.open_price + first_cfdd.close_price +
                          first_cfdd.highest_price +
                          first_cfdd.lowest_price) / 4;
      if first_cfdd.highest_price > v_ha_open_price then
        v_ha_highest_price := first_cfdd.highest_price;
      else
        v_ha_highest_price := v_ha_open_price;
      end if;
      if first_cfdd.lowest_price < v_ha_open_price then
        v_ha_lowest_price := first_cfdd.lowest_price;
      else
        v_ha_lowest_price := v_ha_open_price;
      end if;
      -- 保存hei kin ashi平均K线开盘价，收盘价，最高价和最低价
      update commodity_future_date_data t
         set t.ha_open_price    = v_ha_open_price,
             t.ha_close_price   = v_ha_close_price,
             t.ha_highest_price = v_ha_highest_price,
             t.ha_lowest_price  = v_ha_lowest_price
       where t.code = first_cfdd.code
         and t.transaction_date = first_cfdd.transaction_date;
      commit;
      v_date := first_cfdd.transaction_date;
      close cur_fist_cfdd;
    
      -- 再计算每个期货的其他记录
      open cur_later_cfdd;
      loop
        fetch cur_later_cfdd
          into later_cfdd;
        exit when cur_later_cfdd%notfound;
      
        -- 前一条记录
        select *
          into pre_cfdd
          from (select *
                  from commodity_future_date_data t
                 where t.code = v_code
                   and t.transaction_date <= v_date
                 order by t.transaction_date desc)
         where rownum <= 1;
      
        -- 计算hei kin ashi平均K线开盘价，收盘价，最高价和最低价
        v_ha_open_price  := (pre_cfdd.ha_open_price +
                            pre_cfdd.ha_close_price) / 2;
        v_ha_close_price := (later_cfdd.open_price + later_cfdd.close_price +
                            later_cfdd.highest_price +
                            later_cfdd.lowest_price) / 4;
        if later_cfdd.highest_price > v_ha_open_price then
          v_ha_highest_price := later_cfdd.highest_price;
        else
          v_ha_highest_price := v_ha_open_price;
        end if;
        if later_cfdd.lowest_price < v_ha_open_price then
          v_ha_lowest_price := later_cfdd.lowest_price;
        else
          v_ha_lowest_price := v_ha_open_price;
        end if;
        -- 保存hei kin ashi平均K线开盘价，收盘价，最高价和最低价
        update commodity_future_date_data t
           set t.ha_open_price    = v_ha_open_price,
               t.ha_close_price   = v_ha_close_price,
               t.ha_highest_price = v_ha_highest_price,
               t.ha_lowest_price  = v_ha_lowest_price
         where t.code = later_cfdd.code
           and t.transaction_date = later_cfdd.transaction_date;
        commit;
      
        -- 把这次的数据留给下一次迭代使用
        v_date := later_cfdd.transaction_date;
      end loop;
      close cur_later_cfdd;
    
    end loop;
    close cur_all_code;
  end WRITE_HA;

  /*------------------------------ 计算某一日所有期货的ha -----------------------------------------*/
  procedure WRITE_HA_BY_DATE(p_date varchar2) as
    -- 获取所有的CODE
    cursor cur_all_code is
      select distinct t.code code from commodity_future_date_data t;
    -- 表示CODE类型的变量
    v_code varchar2(10);
    -- 查询某个期货在日期p_date之前的那一条记录
    cursor cur_all_cfdd is
      select *
        from (select *
                from (select *
                        from commodity_future_date_data t
                       where t.code = v_code
                         and t.transaction_date <=
                             to_date(p_date, 'yyyy-mm-dd')
                       order by t.transaction_date desc)
               where rownum <= 2) t2
       where t2.transaction_date <>
             (select t1.transaction_date
                from commodity_future_date_data t1
               where t1.code = v_code
                 and t1.transaction_date = to_date(p_date, 'yyyy-mm-dd'));
    -- 定义表commodity_future_date_data结构的游标变量
    all_cfdd commodity_future_date_data%rowtype;
  
    -- 查询某个具体的期货的某一日交易记录
    cursor cur_later_cfdd is
      select *
        from commodity_future_date_data t
       where t.code = v_code
         and t.transaction_date = to_date(p_date, 'yyyy-mm-dd');
    -- 定义表commodity_future_date_data结构的游标变量
    later_cfdd commodity_future_date_data%rowtype;
  
    -- 用于计算hei kin ashi平均K线开盘价，收盘价，最高价和最低价的变量
    v_ha_open_price    number;
    v_ha_close_price   number;
    v_ha_highest_price number;
    v_ha_lowest_price  number;
  begin
    open cur_all_code;
    loop
      -- 获取每个期货的code字段
      fetch cur_all_code
        into v_code;
      exit when cur_all_code%notfound;
    
      open cur_all_cfdd;
      open cur_later_cfdd;
      loop
        fetch cur_all_cfdd
          into all_cfdd;
        exit when cur_all_cfdd%notfound;
      
        fetch cur_later_cfdd
          into later_cfdd;
        exit when cur_later_cfdd%notfound;
      
        -- 计算hei kin ashi平均K线开盘价，收盘价，最高价和最低价
        v_ha_open_price  := (all_cfdd.ha_open_price +
                            all_cfdd.ha_close_price) / 2;
        v_ha_close_price := (later_cfdd.open_price + later_cfdd.close_price +
                            later_cfdd.highest_price +
                            later_cfdd.lowest_price) / 4;
        if later_cfdd.highest_price > v_ha_open_price then
          v_ha_highest_price := later_cfdd.highest_price;
        else
          v_ha_highest_price := v_ha_open_price;
        end if;
        if later_cfdd.lowest_price < v_ha_open_price then
          v_ha_lowest_price := later_cfdd.lowest_price;
        else
          v_ha_lowest_price := v_ha_open_price;
        end if;
        -- 保存hei kin ashi平均K线开盘价，收盘价，最高价和最低价
        update commodity_future_date_data t
           set t.ha_open_price    = v_ha_open_price,
               t.ha_close_price   = v_ha_close_price,
               t.ha_highest_price = v_ha_highest_price,
               t.ha_lowest_price  = v_ha_lowest_price
         where t.code = later_cfdd.code
           and t.transaction_date = later_cfdd.transaction_date;
        commit;
      end loop;
      close cur_all_cfdd;
      close cur_later_cfdd;
    
    end loop;
    close cur_all_code;
  end WRITE_HA_BY_DATE;

  /*---------------- 计算commodity_future_date_data表中的布林带 ------------------*/
  procedure CAL_BOLL is
    -- 期货代码
    v_code varchar(50);
    -- 布林带中轨、上轨、下轨
    v_mb number;
    v_up number;
    v_dn number;
    -- 计算标准差时使用
    v_sum number := 0;
    -- 记录数量
    v_num number;
    -- 某只期货的一条交易记录
    row_commodity_future_date_data commodity_future_date_data%rowtype;
    row_c_f_date_data_desc         commodity_future_date_data%rowtype;
    -- 周线级别所有期货的code
    cursor cur_code is
      select distinct t.code from commodity_future_date_data t;
    -- 某一只期货的全部交易记录，升序排列
    cursor cur_single is
      select *
        from commodity_future_date_data t
       where t.code = v_code
         and t.ma20 is not null
       order by t.transaction_date asc;
    -- 某只期货，在某日之后的交易记录，降序排列
    cursor cur_desc is
      select *
        from (select *
                from commodity_future_date_data t
               where t.code = row_commodity_future_date_data.code
                 and t.transaction_date <=
                     row_commodity_future_date_data.transaction_date
               order by t.transaction_date desc)
       where rownum <= 20;
  begin
    open cur_code;
    loop
      fetch cur_code
        into v_code;
      exit when cur_code%notfound;
    
      open cur_single;
      loop
        fetch cur_single
          into row_commodity_future_date_data;
        exit when cur_single%notfound;
      
        -- 初始化
        v_up  := 0;
        v_dn  := 0;
        v_sum := 0;
      
        -- 中轨
        select avg(t1.close_price)
          into v_mb
          from (select *
                  from commodity_future_date_data t
                 where t.code = row_commodity_future_date_data.code
                   and t.transaction_date <=
                       row_commodity_future_date_data.transaction_date
                 order by t.transaction_date desc) t1
         where rownum <= 20;
        -- v_mb := row_stock.ma20;
      
        -- 如果交易次数不够20，则返回
        select count(*)
          into v_num
          from commodity_future_date_data t
         where t.code = row_commodity_future_date_data.code
           and t.transaction_date <=
               row_commodity_future_date_data.transaction_date;
        if v_num < 20 then
          continue;
        end if;
      
        open cur_desc;
        loop
          fetch cur_desc
            into row_c_f_date_data_desc;
          exit when cur_desc%notfound;
          v_sum := v_sum +
                   power(row_c_f_date_data_desc.close_price - v_mb, 2);
        end loop;
        close cur_desc;
      
        -- 上轨、下轨
        v_up := v_mb + 2 * sqrt(v_sum / 20);
        v_dn := v_mb - 2 * sqrt(v_sum / 20);
      
        -- 更新记录
        update commodity_future_date_data
           set mb = round(v_mb, 3),
               up = round(v_up, 3),
               dn = round(v_dn, 3)
         where code = row_commodity_future_date_data.code
           and transaction_date =
               row_commodity_future_date_data.transaction_date;
      
      end loop;
      close cur_single;
    end loop;
    close cur_code;
    commit;
  end CAL_BOLL;

  /*-- 计算某一日commodity_future_date_data表中的布林带，必须在计算完均线后运行 --*/
  procedure CAL_BOLL_BY_DATE(p_date varchar2) is
    -- 期货代码
    v_code varchar(50);
    -- 布林带中轨、上轨、下轨
    v_mb number;
    v_up number;
    v_dn number;
    -- 计算标准差时使用
    v_sum number := 0;
    -- 记录数量
    v_num number;
    -- 某只期货的一条交易记录
    row_c_f_date_data_desc commodity_future_date_data%rowtype;
    -- 期货的code
    cursor cur_code is
      select distinct t.code
        from commodity_future_date_data t
       where t.transaction_date = to_date(p_date, 'yyyy-mm-dd');
    -- 某一只期货的全部交易记录，降序排列
    /*cursor cur_single_stock is
    select *
      from stock_transaction_data t
     where t.code_ = v_code
       and t.date_ <= to_date(p_date, 'yyyy-mm-dd')
       and t.ma20 is not null
     order by t.date_ desc;*/
    -- 某只期货，在某日之后的交易记录，降序排列
    cursor cur_desc is
      select *
        from (select *
                from commodity_future_date_data t
               where t.code = v_code
                 and t.transaction_date <= to_date(p_date, 'yyyy-mm-dd')
               order by t.transaction_date desc)
       where rownum <= 20;
  begin
    open cur_code;
    loop
      fetch cur_code
        into v_code;
      exit when cur_code%notfound;
    
      /*open cur_single_stock;
      loop
        fetch cur_single_stock
          into row_stock;
        exit when cur_single_stock%notfound;*/
    
      -- 如果交易次数不够20，则返回
      select count(*)
        into v_num
        from commodity_future_date_data t
       where t.code = v_code
         and t.transaction_date <= to_date(p_date, 'yyyy-mm-dd');
      if v_num < 20 then
        continue;
      end if;
    
      -- 初始化
      v_up  := 0;
      v_dn  := 0;
      v_sum := 0;
    
      -- 中轨
      select avg(t1.close_price)
        into v_mb
        from (select *
                from commodity_future_date_data t
               where t.code = v_code
                 and t.transaction_date <= to_date(p_date, 'yyyy-mm-dd')
               order by t.transaction_date desc) t1
       where rownum <= 20;
      -- v_mb := row_stock.ma20;
    
      open cur_desc;
      loop
        fetch cur_desc
          into row_c_f_date_data_desc;
        exit when cur_desc%notfound;
        v_sum := v_sum +
                 power(row_c_f_date_data_desc.close_price - v_mb, 2);
      end loop;
      close cur_desc;
    
      -- 上轨、下轨
      v_up := v_mb + 2 * sqrt(v_sum / 20);
      v_dn := v_mb - 2 * sqrt(v_sum / 20);
    
      -- 更新记录
      update commodity_future_date_data
         set mb = round(v_mb, 3), up = round(v_up, 3), dn = round(v_dn, 3)
       where code = v_code
         and transaction_date = to_date(p_date, 'yyyy-mm-dd');
    
      /*end loop;
      close cur_single_stock;*/
    end loop;
    close cur_code;
    commit;
  end CAL_BOLL_BY_DATE;

  /*---------------------------------------------------- 计算所有期货的乖离率 -------------------------------------------------*/
  procedure WRITE_BIAS is
  begin
    declare
      -- 表示code
      v_code varchar2(10);
      -- 5日乖离率
      v_bias5 number := null;
      -- 10日乖离率
      v_bias10 number := null;
      -- 20日乖离率
      v_bias20 number := null;
      -- 60日乖离率
      v_bias60 number := null;
      -- 120日乖离率
      v_bias120 number := null;
      -- 250日乖离率
      v_bias250 number := null;
      -- 交易日期
      v_date date;
      -- 获取期货的code，排除重复
      cursor cur_all_code is
        select distinct t.code from commodity_future_date_data t;
      -- 根据v_code，获取这个期货的所有数据，并按照升序排列
      cursor cur_single is
        select *
          from commodity_future_date_data t
         where t.code = v_code
         order by t.transaction_date asc;
    begin
      for i in cur_all_code loop
        v_code := i.code;
        for j in cur_single loop
          v_date := j.transaction_date;
          -- 5日乖离率
          if j.ma5 is not null then
            v_bias5 := (j.close_price - j.ma5) / j.ma5 * 100;
          else
            v_bias5 := null;
          end if;
          -- 10日乖离率
          if j.ma10 is not null then
            v_bias10 := (j.close_price - j.ma10) / j.ma10 * 100;
          else
            v_bias10 := null;
          end if;
          -- 20日乖离率
          if j.ma20 is not null then
            v_bias20 := (j.close_price - j.ma20) / j.ma20 * 100;
          else
            v_bias20 := null;
          end if;
          -- 60日乖离率
          if j.ma60 is not null then
            v_bias60 := (j.close_price - j.ma60) / j.ma60 * 100;
          else
            v_bias60 := null;
          end if;
          -- 120日乖离率
          if j.ma120 is not null then
            v_bias120 := (j.close_price - j.ma120) / j.ma120 * 100;
          else
            v_bias120 := null;
          end if;
          -- 250日乖离率
          if j.ma250 is not null then
            v_bias250 := (j.close_price - j.ma250) / j.ma250 * 100;
          else
            v_bias250 := null;
          end if;
        
          -- 更新
          update commodity_future_date_data t
             set t.bias5   = v_bias5,
                 t.bias10  = v_bias10,
                 t.bias20  = v_bias20,
                 t.bias60  = v_bias60,
                 t.bias120 = v_bias120,
                 t.bias250 = v_bias250
           where t.code = v_code
             and t.transaction_date = v_date;
        end loop;
        commit;
      end loop;
    end;
  end WRITE_BIAS;

  /*------------------------ 按照日期，计算所有期货在某一日的乖离率 -----------------------*/
  procedure WRITE_BIAS_BY_DATE(p_date in varchar2) is
    -- 表示CODE类型的变量
    v_code varchar2(10);
    -- 5日乖离率
    v_bias5 number := null;
    -- 10日乖离率
    v_bias10 number := null;
    -- 20日乖离率
    v_bias20 number := null;
    -- 60日乖离率
    v_bias60 number := null;
    -- 120日乖离率
    v_bias120 number := null;
    -- 250日乖离率
    v_bias250 number := null;
  
    row_commodity_future_date_data commodity_future_date_data%rowtype;
    -- 获取所有期货的code，排除重复的
    cursor cur_all_code is
      select distinct t.code from commodity_future_date_data t;
    -- 某个期货某一天的交易记录
    cursor cur_single is
      select *
        from commodity_future_date_data t
       where t.code = v_code
         and t.transaction_date = to_date(p_date, 'yyyy-mm-dd');
  begin
    open cur_all_code;
    loop
      -- 获取每个期货的code字段
      fetch cur_all_code
        into v_code;
      exit when cur_all_code%notfound;
    
      open cur_single;
      loop
        -- 获取每个期货在某一日的交易记录
        fetch cur_single
          into row_commodity_future_date_data;
        exit when cur_single%notfound;
      
        -- 计算5日乖离率、10日乖离率、20日乖离率、60日乖离率、120日乖离率、250日乖离率
        if row_commodity_future_date_data.ma5 is not null then
          v_bias5 := (row_commodity_future_date_data.close_price -
                     row_commodity_future_date_data.ma5) /
                     row_commodity_future_date_data.ma5 * 100;
        else
          v_bias5 := null;
        end if;
        if row_commodity_future_date_data.ma10 is not null then
          v_bias10 := (row_commodity_future_date_data.close_price -
                      row_commodity_future_date_data.ma10) /
                      row_commodity_future_date_data.ma10 * 100;
        else
          v_bias10 := null;
        end if;
        if row_commodity_future_date_data.ma20 is not null then
          v_bias20 := (row_commodity_future_date_data.close_price -
                      row_commodity_future_date_data.ma20) /
                      row_commodity_future_date_data.ma20 * 100;
        else
          v_bias20 := null;
        end if;
        if row_commodity_future_date_data.ma60 is not null then
          v_bias60 := (row_commodity_future_date_data.close_price -
                      row_commodity_future_date_data.ma60) /
                      row_commodity_future_date_data.ma60 * 100;
        else
          v_bias60 := null;
        end if;
        if row_commodity_future_date_data.ma120 is not null then
          v_bias120 := (row_commodity_future_date_data.close_price -
                       row_commodity_future_date_data.ma120) /
                       row_commodity_future_date_data.ma120 * 100;
        else
          v_bias120 := null;
        end if;
        if row_commodity_future_date_data.ma250 is not null then
          v_bias250 := (row_commodity_future_date_data.close_price -
                       row_commodity_future_date_data.ma250) /
                       row_commodity_future_date_data.ma250 * 100;
        else
          v_bias250 := null;
        end if;
      
        -- 更新某个期货在某一日的5日乖离率、10日乖离率、20日乖离率、60日乖离率、120日乖离率、250日乖离率
        update commodity_future_date_data t
           set t.bias5   = v_bias5,
               t.bias10  = v_bias10,
               t.bias20  = v_bias20,
               t.bias60  = v_bias60,
               t.bias120 = v_bias120,
               t.bias250 = v_bias250
         where t.code = v_code
           and t.transaction_date = to_date(p_date, 'yyyy-mm-dd');
      
      end loop;
      close cur_single;
    
      commit;
    
    end loop;
    close cur_all_code;
  end WRITE_BIAS_BY_DATE;

  /*------------------------------ 计算所有期货的方差 -----------------------------------------*/
  procedure WRITE_VARIANCE is
    -- 期货code
    v_code varchar2(10);
    -- 期货date
    v_date date;
    -- 方差
    v_variance5   number;
    v_variance10  number;
    v_variance20  number;
    v_variance60  number;
    v_variance120 number;
    v_variance250 number;
    -- 计算方差时用到的变量
    v_sum5   number;
    v_sum10  number;
    v_sum20  number;
    v_sum60  number;
    v_sum120 number;
    v_sum250 number;
    -- 均线
    v_ma5   number;
    v_ma10  number;
    v_ma20  number;
    v_ma60  number;
    v_ma120 number;
    v_ma250 number;
    -- 需要计算的记录数
    v_rownum number;
    -- 所有期货的code
    cursor cur_all_code is
      select distinct t.code from commodity_future_date_data t;
    -- 某一只期货的全部交易日期，并降序排列
    cursor cur_cfdd_by_code is
      select *
        from commodity_future_date_data t
       where t.code = v_code
       order by t.transaction_date desc;
    -- 某只期货、在某个日期之前的全部记录，按日期降序排列
    cursor cur_cfdd_by_code_date is
      select *
        from (select *
                from commodity_future_date_data t
               where t.code = v_code
                 and t.transaction_date <= v_date
               order by t.transaction_date desc)
       where rownum <= v_rownum;
  begin
    for i in cur_all_code loop
      v_code := i.code;
    
      for j in cur_cfdd_by_code loop
        v_date := j.transaction_date;
      
        v_ma5   := j.ma5;
        v_ma10  := j.ma10;
        v_ma20  := j.ma20;
        v_ma60  := j.ma60;
        v_ma120 := j.ma120;
        v_ma250 := j.ma250;
      
        -- 重置
        v_variance5   := null;
        v_variance10  := null;
        v_variance20  := null;
        v_variance60  := null;
        v_variance120 := null;
        v_variance250 := null;
        v_sum5        := 0;
        v_sum10       := 0;
        v_sum20       := 0;
        v_sum60       := 0;
        v_sum120      := 0;
        v_sum250      := 0;
      
        -- 计算variance5
        if v_ma5 is not null then
          v_rownum := 5;
          for x in cur_cfdd_by_code_date loop
            v_sum5 := v_sum5 + power(x.close_price - v_ma5, 2);
          end loop;
          v_variance5 := 1 / (v_rownum - 1) * v_sum5;
        end if;
      
        -- 计算variance10
        if v_ma10 is not null then
          v_rownum := 10;
          for x in cur_cfdd_by_code_date loop
            v_sum10 := v_sum10 + power(x.close_price - v_ma10, 2);
          end loop;
          v_variance10 := 1 / (v_rownum - 1) * v_sum10;
        end if;
      
        -- 计算variance20
        if v_ma20 is not null then
          v_rownum := 20;
          for x in cur_cfdd_by_code_date loop
            v_sum20 := v_sum20 + power(x.close_price - v_ma20, 2);
          end loop;
          v_variance20 := 1 / (v_rownum - 1) * v_sum20;
        end if;
      
        -- 计算variance60
        if v_ma60 is not null then
          v_rownum := 60;
          for x in cur_cfdd_by_code_date loop
            v_sum60 := v_sum60 + power(x.close_price - v_ma60, 2);
          end loop;
          v_variance60 := 1 / (v_rownum - 1) * v_sum60;
        end if;
      
        -- 计算variance120
        if v_ma120 is not null then
          v_rownum := 120;
          for x in cur_cfdd_by_code_date loop
            v_sum120 := v_sum120 + power(x.close_price - v_ma120, 2);
          end loop;
          v_variance120 := 1 / (v_rownum - 1) * v_sum120;
        end if;
      
        -- 计算variance250
        if v_ma250 is not null then
          v_rownum := 250;
          for x in cur_cfdd_by_code_date loop
            v_sum250 := v_sum250 + power(x.close_price - v_ma250, 2);
          end loop;
          v_variance250 := 1 / (v_rownum - 1) * v_sum250;
        end if;
      
        -- 更新
        update commodity_future_date_data t
           set t.variance5   = v_variance5,
               t.variance10  = v_variance10,
               t.variance20  = v_variance20,
               t.variance60  = v_variance60,
               t.variance120 = v_variance120,
               t.variance250 = v_variance250
         where t.code = v_code
           and t.transaction_date = v_date;
      end loop;
    end loop;
    commit;
  end WRITE_VARIANCE;

  /*------------------------------ 按照日期，计算所有期货在某一日的的方差 -----------------------------------------*/
  procedure WRITE_VARIANCE_BY_DATE(p_date in varchar2) is
    -- 期货code
    v_code varchar2(10);
    -- 期货date
    --v_date date;
    -- 方差
    v_variance5   number;
    v_variance10  number;
    v_variance20  number;
    v_variance60  number;
    v_variance120 number;
    v_variance250 number;
    -- 计算方差时用到的变量
    v_sum5   number;
    v_sum10  number;
    v_sum20  number;
    v_sum60  number;
    v_sum120 number;
    v_sum250 number;
    -- 均线
    v_ma5   number;
    v_ma10  number;
    v_ma20  number;
    v_ma60  number;
    v_ma120 number;
    v_ma250 number;
    -- 需要计算的记录数
    v_rownum number;
    -- commodity_future_date_data类型的变量
    row_commodity_future_date_data commodity_future_date_data%rowtype;
    -- 所有期货的code
    cursor cur_all_code is
      select distinct t.code from commodity_future_date_data t;
    -- 某只期货、在某个日期之前的全部记录，按日期降序排列
    cursor cur_cfdd_by_code_date is
      select *
        from (select *
                from commodity_future_date_data t
               where t.code = v_code
                 and t.transaction_date <= to_date(p_date, 'yyyy-mm-dd')
               order by t.transaction_date desc)
       where rownum <= v_rownum;
  begin
    for i in cur_all_code loop
      v_code := i.code;
    
      begin
        select *
          into row_commodity_future_date_data
          from commodity_future_date_data t
         where t.code = v_code
           and t.transaction_date = to_date(p_date, 'yyyy-mm-dd');
      exception
        when NO_DATA_FOUND then
          dbms_output.put_line('commodity_future_date_data表中，没有code为【' ||
                               v_code || '】，transaction_date为【' || p_date ||
                               '】时的记录');
          continue;
      end;
    
      v_ma5   := row_commodity_future_date_data.ma5;
      v_ma10  := row_commodity_future_date_data.ma10;
      v_ma20  := row_commodity_future_date_data.ma20;
      v_ma60  := row_commodity_future_date_data.ma60;
      v_ma120 := row_commodity_future_date_data.ma120;
      v_ma250 := row_commodity_future_date_data.ma250;
    
      -- 重置
      v_variance5   := null;
      v_variance10  := null;
      v_variance20  := null;
      v_variance60  := null;
      v_variance120 := null;
      v_variance250 := null;
      v_sum5        := 0;
      v_sum10       := 0;
      v_sum20       := 0;
      v_sum60       := 0;
      v_sum120      := 0;
      v_sum250      := 0;
    
      -- 计算variance5
      if v_ma5 is not null then
        v_rownum := 5;
        for x in cur_cfdd_by_code_date loop
          v_sum5 := v_sum5 + power(x.close_price - v_ma5, 2);
        end loop;
        v_variance5 := 1 / (v_rownum - 1) * v_sum5;
      end if;
    
      -- 计算variance10
      if v_ma10 is not null then
        v_rownum := 10;
        for x in cur_cfdd_by_code_date loop
          v_sum10 := v_sum10 + power(x.close_price - v_ma10, 2);
        end loop;
        v_variance10 := 1 / (v_rownum - 1) * v_sum10;
      end if;
    
      -- 计算variance20
      if v_ma20 is not null then
        v_rownum := 20;
        for x in cur_cfdd_by_code_date loop
          v_sum20 := v_sum20 + power(x.close_price - v_ma20, 2);
        end loop;
        v_variance20 := 1 / (v_rownum - 1) * v_sum20;
      end if;
    
      -- 计算variance60
      if v_ma60 is not null then
        v_rownum := 60;
        for x in cur_cfdd_by_code_date loop
          v_sum60 := v_sum60 + power(x.close_price - v_ma60, 2);
        end loop;
        v_variance60 := 1 / (v_rownum - 1) * v_sum60;
      end if;
    
      -- 计算variance120
      if v_ma120 is not null then
        v_rownum := 120;
        for x in cur_cfdd_by_code_date loop
          v_sum120 := v_sum120 + power(x.close_price - v_ma120, 2);
        end loop;
        v_variance120 := 1 / (v_rownum - 1) * v_sum120;
      end if;
    
      -- 计算variance250
      if v_ma250 is not null then
        v_rownum := 250;
        for x in cur_cfdd_by_code_date loop
          v_sum250 := v_sum250 + power(x.close_price - v_ma250, 2);
        end loop;
        v_variance250 := 1 / (v_rownum - 1) * v_sum250;
      end if;
    
      -- 更新
      update commodity_future_date_data t
         set t.variance5   = v_variance5,
             t.variance10  = v_variance10,
             t.variance20  = v_variance20,
             t.variance60  = v_variance60,
             t.variance120 = v_variance120,
             t.variance250 = v_variance250
       where t.code = v_code
         and t.transaction_date = to_date(p_date, 'yyyy-mm-dd');
    end loop;
    commit;
  end WRITE_VARIANCE_BY_DATE;

end PKG_COMMODITY_FUTURE_DATE_DATA;